<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <meta name="generator" content="VuePress 2.0.0-beta.66">
    <style>
      :root {
        --c-bg: #fff;
      }
      html.dark {
        --c-bg: #22272e;
      }
      html, body {
        background-color: var(--c-bg);
      }
    </style>
    <script>
      const userMode = localStorage.getItem('vuepress-color-scheme');
			const systemDarkMode = window.matchMedia && window.matchMedia('(prefers-color-scheme: dark)').matches;
			if (userMode === 'dark' || (userMode !== 'light' && systemDarkMode)) {
				document.documentElement.classList.toggle('dark', true);
			}
    </script>
    <title>数据库 | 你好， VuePress ！</title><meta name="description" content="这是我的第一个 VuePress 站点">
    <link rel="preload" href="/assets/style-8a38cc2d.css" as="style"><link rel="stylesheet" href="/assets/style-8a38cc2d.css">
    <link rel="modulepreload" href="/assets/app-6c74e544.js"><link rel="modulepreload" href="/assets/数据库.html-a9b825b9.js"><link rel="modulepreload" href="/assets/数据库.html-e55ff260.js"><link rel="prefetch" href="/assets/index.html-75ca5b8c.js" as="script"><link rel="prefetch" href="/assets/化学.html-bcd92d03.js" as="script"><link rel="prefetch" href="/assets/IDEA.html-ce9ad873.js" as="script"><link rel="prefetch" href="/assets/python调用idm下载.html-615c23bf.js" as="script"><link rel="prefetch" href="/assets/index.html-571c49cc.js" as="script"><link rel="prefetch" href="/assets/VuePress2x搭建.html-910a2c4e.js" as="script"><link rel="prefetch" href="/assets/WindowsTerminal.html-4a75819f.js" as="script"><link rel="prefetch" href="/assets/乱七八糟.html-0ad5c8cc.js" as="script"><link rel="prefetch" href="/assets/国内访问GitHub.html-bd9bc6eb.js" as="script"><link rel="prefetch" href="/assets/虚拟机安装凤凰OS.html-fd472c84.js" as="script"><link rel="prefetch" href="/assets/软件工程过程.html-ad186a25.js" as="script"><link rel="prefetch" href="/assets/Java.html-a9e8e4b2.js" as="script"><link rel="prefetch" href="/assets/latex.html-b66b07c2.js" as="script"><link rel="prefetch" href="/assets/Markdown.html-76abef01.js" as="script"><link rel="prefetch" href="/assets/Markdown_vuepress.html-7a108572.js" as="script"><link rel="prefetch" href="/assets/Numpy.html-742cd1c8.js" as="script"><link rel="prefetch" href="/assets/python.html-80001bc2.js" as="script"><link rel="prefetch" href="/assets/python爬虫.html-0eaf5c34.js" as="script"><link rel="prefetch" href="/assets/index.html-404afaec.js" as="script"><link rel="prefetch" href="/assets/Redis.html-3c0347e6.js" as="script"><link rel="prefetch" href="/assets/Selenium.html-dd74eb14.js" as="script"><link rel="prefetch" href="/assets/快速幂算法 牛客小白月赛 1-C分元宵.html-6a00d92c.js" as="script"><link rel="prefetch" href="/assets/数据结构_学校.html-db54f988.js" as="script"><link rel="prefetch" href="/assets/404.html-f9875e7b.js" as="script"><link rel="prefetch" href="/assets/index.html-f9be7961.js" as="script"><link rel="prefetch" href="/assets/化学.html-55546e87.js" as="script"><link rel="prefetch" href="/assets/IDEA.html-3d6ee1dc.js" as="script"><link rel="prefetch" href="/assets/python调用idm下载.html-862a9ae4.js" as="script"><link rel="prefetch" href="/assets/index.html-71e4b30e.js" as="script"><link rel="prefetch" href="/assets/VuePress2x搭建.html-69ed53cb.js" as="script"><link rel="prefetch" href="/assets/WindowsTerminal.html-d051c0b9.js" as="script"><link rel="prefetch" href="/assets/乱七八糟.html-e6e19f4d.js" as="script"><link rel="prefetch" href="/assets/国内访问GitHub.html-5a1e283c.js" as="script"><link rel="prefetch" href="/assets/虚拟机安装凤凰OS.html-3f19c5ba.js" as="script"><link rel="prefetch" href="/assets/软件工程过程.html-d5033030.js" as="script"><link rel="prefetch" href="/assets/Java.html-07b672b0.js" as="script"><link rel="prefetch" href="/assets/latex.html-139cf75a.js" as="script"><link rel="prefetch" href="/assets/Markdown.html-24c02676.js" as="script"><link rel="prefetch" href="/assets/Markdown_vuepress.html-a9f40501.js" as="script"><link rel="prefetch" href="/assets/Numpy.html-6ea20a5f.js" as="script"><link rel="prefetch" href="/assets/python.html-da27b5e1.js" as="script"><link rel="prefetch" href="/assets/python爬虫.html-22e08aed.js" as="script"><link rel="prefetch" href="/assets/index.html-25c668e6.js" as="script"><link rel="prefetch" href="/assets/Redis.html-732c58b4.js" as="script"><link rel="prefetch" href="/assets/Selenium.html-8840df78.js" as="script"><link rel="prefetch" href="/assets/快速幂算法 牛客小白月赛 1-C分元宵.html-41e7e00f.js" as="script"><link rel="prefetch" href="/assets/数据结构_学校.html-cf35a9b9.js" as="script"><link rel="prefetch" href="/assets/404.html-ad53950d.js" as="script"><link rel="prefetch" href="/assets/mermaid-9e549946.js" as="script">
  </head>
  <body>
    <div id="app"><!--[--><div class="theme-container"><!--[--><header class="navbar"><div class="toggle-sidebar-button" title="toggle sidebar" aria-expanded="false" role="button" tabindex="0"><div class="icon" aria-hidden="true"><span></span><span></span><span></span></div></div><span><a href="/" class=""><img class="logo" src="/img/hero.png" alt="你好， VuePress ！"><span class="site-name can-hide">你好， VuePress ！</span></a></span><div class="navbar-items-wrapper" style=""><!--[--><!--]--><nav class="navbar-items can-hide"><!--[--><div class="navbar-item"><div class="navbar-dropdown-wrapper"><button class="navbar-dropdown-title" type="button" aria-label="编程"><span class="title">编程</span><span class="arrow down"></span></button><button class="navbar-dropdown-title-mobile" type="button" aria-label="编程"><span class="title">编程</span><span class="right arrow"></span></button><ul style="display:none;" class="navbar-dropdown"><!--[--><li class="navbar-dropdown-item"><a href="/program/Java.md" class="" aria-label="Java"><!--[--><!--]--> Java <!--[--><!--]--></a></li><li class="navbar-dropdown-item"><a href="/program/Python.md" class="" aria-label="Python"><!--[--><!--]--> Python <!--[--><!--]--></a></li><li class="navbar-dropdown-item"><!--[--><h4 class="navbar-dropdown-subtitle"><span>笔记</span></h4><ul class="navbar-dropdown-subitem-wrapper"><!--[--><li class="navbar-dropdown-subitem"><a href="/program/Markdown.html" class="" aria-label="Markdown"><!--[--><!--]--> Markdown <!--[--><!--]--></a></li><li class="navbar-dropdown-subitem"><a href="/program/latex.html" class="" aria-label="Latex"><!--[--><!--]--> Latex <!--[--><!--]--></a></li><!--]--></ul><!--]--></li><li class="navbar-dropdown-item"><a href="/program/数据库.md" class="" aria-label="数据库"><!--[--><!--]--> 数据库 <!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-item"><a href="/hx/化学.md" class="" aria-label="化学"><!--[--><!--]--> 化学 <!--[--><!--]--></a></div><div class="navbar-item"><a href="/others/" class="" aria-label="乱七八糟"><!--[--><!--]--> 乱七八糟 <!--[--><!--]--></a></div><div class="navbar-item"><a class="external-link" href="https://gitee.com/Francis-xsc/francis-xsc" rel="noopener noreferrer" target="_blank" aria-label="Gitee"><!--[--><!--]--> Gitee <span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span><!--[--><!--]--></a></div><!--]--></nav><!--[--><!--]--><button class="toggle-color-mode-button" title="toggle color mode"><svg style="" class="icon" focusable="false" viewBox="0 0 32 32"><path d="M16 12.005a4 4 0 1 1-4 4a4.005 4.005 0 0 1 4-4m0-2a6 6 0 1 0 6 6a6 6 0 0 0-6-6z" fill="currentColor"></path><path d="M5.394 6.813l1.414-1.415l3.506 3.506L8.9 10.318z" fill="currentColor"></path><path d="M2 15.005h5v2H2z" fill="currentColor"></path><path d="M5.394 25.197L8.9 21.691l1.414 1.415l-3.506 3.505z" fill="currentColor"></path><path d="M15 25.005h2v5h-2z" fill="currentColor"></path><path d="M21.687 23.106l1.414-1.415l3.506 3.506l-1.414 1.414z" fill="currentColor"></path><path d="M25 15.005h5v2h-5z" fill="currentColor"></path><path d="M21.687 8.904l3.506-3.506l1.414 1.415l-3.506 3.505z" fill="currentColor"></path><path d="M15 2.005h2v5h-2z" fill="currentColor"></path></svg><svg style="display:none;" class="icon" focusable="false" viewBox="0 0 32 32"><path d="M13.502 5.414a15.075 15.075 0 0 0 11.594 18.194a11.113 11.113 0 0 1-7.975 3.39c-.138 0-.278.005-.418 0a11.094 11.094 0 0 1-3.2-21.584M14.98 3a1.002 1.002 0 0 0-.175.016a13.096 13.096 0 0 0 1.825 25.981c.164.006.328 0 .49 0a13.072 13.072 0 0 0 10.703-5.555a1.01 1.01 0 0 0-.783-1.565A13.08 13.08 0 0 1 15.89 4.38A1.015 1.015 0 0 0 14.98 3z" fill="currentColor"></path></svg></button><!----></div></header><!--]--><div class="sidebar-mask"></div><!--[--><aside class="sidebar"><nav class="navbar-items"><!--[--><div class="navbar-item"><div class="navbar-dropdown-wrapper"><button class="navbar-dropdown-title" type="button" aria-label="编程"><span class="title">编程</span><span class="arrow down"></span></button><button class="navbar-dropdown-title-mobile" type="button" aria-label="编程"><span class="title">编程</span><span class="right arrow"></span></button><ul style="display:none;" class="navbar-dropdown"><!--[--><li class="navbar-dropdown-item"><a href="/program/Java.md" class="" aria-label="Java"><!--[--><!--]--> Java <!--[--><!--]--></a></li><li class="navbar-dropdown-item"><a href="/program/Python.md" class="" aria-label="Python"><!--[--><!--]--> Python <!--[--><!--]--></a></li><li class="navbar-dropdown-item"><!--[--><h4 class="navbar-dropdown-subtitle"><span>笔记</span></h4><ul class="navbar-dropdown-subitem-wrapper"><!--[--><li class="navbar-dropdown-subitem"><a href="/program/Markdown.html" class="" aria-label="Markdown"><!--[--><!--]--> Markdown <!--[--><!--]--></a></li><li class="navbar-dropdown-subitem"><a href="/program/latex.html" class="" aria-label="Latex"><!--[--><!--]--> Latex <!--[--><!--]--></a></li><!--]--></ul><!--]--></li><li class="navbar-dropdown-item"><a href="/program/数据库.md" class="" aria-label="数据库"><!--[--><!--]--> 数据库 <!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-item"><a href="/hx/化学.md" class="" aria-label="化学"><!--[--><!--]--> 化学 <!--[--><!--]--></a></div><div class="navbar-item"><a href="/others/" class="" aria-label="乱七八糟"><!--[--><!--]--> 乱七八糟 <!--[--><!--]--></a></div><div class="navbar-item"><a class="external-link" href="https://gitee.com/Francis-xsc/francis-xsc" rel="noopener noreferrer" target="_blank" aria-label="Gitee"><!--[--><!--]--> Gitee <span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span><!--[--><!--]--></a></div><!--]--></nav><!--[--><!--]--><ul class="sidebar-items"><!--[--><li><a href="/program/Java.md" class="sidebar-item sidebar-heading collapsible" aria-label="Java"><!--[--><!--]--> Java <!--[--><!--]--></a><!----></li><li><p tabindex="0" class="sidebar-item sidebar-heading collapsible">Python <span class="right arrow"></span></p><ul style="display:none;" class="sidebar-item-children"><!--[--><li><a href="/program/python.html" class="sidebar-item" aria-label="python-北理工"><!--[--><!--]--> python-北理工 <!--[--><!--]--></a><!----></li><!--]--></ul></li><li><p tabindex="0" class="sidebar-item sidebar-heading collapsible">Markdown <span class="right arrow"></span></p><ul style="display:none;" class="sidebar-item-children"><!--[--><li><a href="/program/Markdown.html" class="sidebar-item" aria-label="Markdown"><!--[--><!--]--> Markdown <!--[--><!--]--></a><!----></li><!--]--></ul></li><!--]--></ul><!--[--><!--]--></aside><!--]--><!--[--><main class="page"><!--[--><!--]--><div class="theme-default-content"><!--[--><!--]--><div><h1 id="数据库" tabindex="-1"><a class="header-anchor" href="#数据库" aria-hidden="true">#</a> 数据库</h1><h3 id="mysql服务的启动和停止" tabindex="-1"><a class="header-anchor" href="#mysql服务的启动和停止" aria-hidden="true">#</a> MySQL服务的启动和停止</h3><p>net stop</p><p>net start</p><h3 id="mysql服务端的登录和退出" tabindex="-1"><a class="header-anchor" href="#mysql服务端的登录和退出" aria-hidden="true">#</a> MySQL服务端的登录和退出</h3><p>法一：通过MySQL自带的客户端</p><p>只限于root用户</p><p>方式二：</p><p>通过Windows自带的客户端</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>mysql [-h主机名 -P端口号] -u用户名 -p密码
mysql -uroot -p
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><p>退出:exit或ctrl+c</p><h3 id="mysql常见命令" tabindex="-1"><a class="header-anchor" href="#mysql常见命令" aria-hidden="true">#</a> MySQL常见命令</h3><p>查看当前所有数据库:<code>show database;</code></p><p>打开指定的库:<code>use 库名</code></p><p>查看当前库的所有表<code>show tables;</code></p><p>查看其他库的所有表:<code>show tables from 库名</code></p><p><code>select database();</code> 查看当前所在库</p><p>创建表:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">table</span> 表名<span class="token punctuation">(</span>
	列名 列类型<span class="token punctuation">,</span>
    列明 列类型<span class="token punctuation">,</span>
    <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">)</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> stuinfo<span class="token punctuation">(</span>
    id <span class="token keyword">int</span><span class="token punctuation">,</span>
    name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>查看表结构:<code>desc stuindo;</code></p><p>看表中有哪些数据</p><p><code>select * from stuinfo</code>;</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>insert into stuinfo(id,name) values(1,&#39;john&#39;);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>修改 <code>update stuintfo set name =&#39;lilei&#39; where id=1;</code></p><p>删除 <code>delete from stuinfo where id=1;</code></p><p>查看服务器版本</p><p>方式一:登录到mysql客户端</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>select version();
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>方式二:没有登录到mysql客户端</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>mysql --version
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>或</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>mysql --V
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h3 id="mysql语法规范" tabindex="-1"><a class="header-anchor" href="#mysql语法规范" aria-hidden="true">#</a> MySQL语法规范</h3><ol><li><p>不区分大小写，但建议关键字大写， 表名，列名小写</p></li><li><p>每条命令最好用分号结尾</p></li><li><p>每条命令根据需要，可以进行缩进或换行</p></li><li><p>注释</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>#单行注释
-- 注释文字（有空格）
/*
多行注释
*/
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div></li></ol><h2 id="dql语言" tabindex="-1"><a class="header-anchor" href="#dql语言" aria-hidden="true">#</a> DQL语言</h2><p>Data Query language数据库查询语言</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>#基础查询
语法：
select 查询列表 from 表名;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>特点：</p><ul><li>查询列表可以是：表中的字段、常量值、表达式、函数</li><li>查询的结果是一个虚拟的表格</li></ul><ol><li><p>查询表中的单个字段</p><p><code>select last_name form employees;</code></p></li><li><p>查询表中的多个字段 <code>select last_name,salary,email form employees;</code></p></li><li><p>查询表中的所有字段</p><p><code>select * from employees;</code></p></li><li><p>查询常量值</p><p><code>SELECT 100;</code></p><p><code>SELECT &#39;john&#39;;</code></p></li><li><p>查询表达式</p><p><code>SELECT 100*98;</code></p></li><li><p>查询函数</p><p><code>SELECT VERSION();</code></p></li><li><p>起别名</p><p><code>SELECT ... AS ...</code></p><p><code>select lastname 姓</code></p><ul><li>便于理解</li><li>如果要查询的字段有重名的情况，使用别名可以区分开来</li></ul></li><li><p>去重</p><p><code>SELECT DISTINCT ... from ...</code></p></li><li><p>+的作用:运算符</p><ul><li>两个操作数都为数值型，则做加法运算</li><li>其中一方为字符型，试图将字符型数值转换成数值型</li><li>如果转换成功，则继续做加法运算</li><li>如果转换失败，则将字符型数值转换成0</li><li>只要其中一方为null，则结果肯定为null</li></ul><p>例：查询员工名和姓连接成一个字段，并显示成姓名</p><p>SELECT last_name+first_name as 姓名 from employees;</p></li></ol><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>select concat(last_name,first_name) as 姓名
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>ifnull(cc,0)</p><h2 id="条件查询" tabindex="-1"><a class="header-anchor" href="#条件查询" aria-hidden="true">#</a> 条件查询</h2><h3 id="语法" tabindex="-1"><a class="header-anchor" href="#语法" aria-hidden="true">#</a> 语法：</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span>
	查询列表	<span class="token number">3</span>
<span class="token keyword">from</span>
	表名			<span class="token number">1</span>
<span class="token keyword">where</span>
	筛选条件<span class="token punctuation">;</span>	<span class="token number">2</span>
	
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>分类：</p><ul><li>按条件表达式筛选 条件表达式：&gt; &lt; = != &lt;&gt; &gt;= &lt;=</li><li>按逻辑表达式筛选 逻辑运算符：&amp;&amp;|| ! and or not</li><li>模糊查询 <ul><li>like</li><li>between and</li><li>in</li><li>is null</li></ul></li></ul><h4 id="按条件表达式筛选" tabindex="-1"><a class="header-anchor" href="#按条件表达式筛选" aria-hidden="true">#</a> 按条件表达式筛选</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code>查询工资<span class="token operator">&gt;</span><span class="token number">12000</span>的员工信息
<span class="token keyword">SELECT</span>
	<span class="token operator">*</span>
<span class="token keyword">FROM</span>
	employees
<span class="token keyword">WHERE</span>
	salary<span class="token operator">&gt;</span><span class="token number">12000</span><span class="token punctuation">;</span>
<span class="token comment">---------------------------</span>
查询部门编号不等于<span class="token number">90</span>号的员工名和部门编号
<span class="token keyword">SELECT</span>
	last_name<span class="token punctuation">,</span>
	department_id
<span class="token keyword">from</span>
	employees
<span class="token keyword">where</span>
	department_id <span class="token operator">&lt;&gt;</span> <span class="token number">90</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="按逻辑表达式筛选" tabindex="-1"><a class="header-anchor" href="#按逻辑表达式筛选" aria-hidden="true">#</a> 按逻辑表达式筛选</h4><h4 id="模糊查询" tabindex="-1"><a class="header-anchor" href="#模糊查询" aria-hidden="true">#</a> 模糊查询</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code>查询员工名中包含字符a的员工信息
<span class="token keyword">SELECT</span>
	<span class="token operator">*</span>
<span class="token keyword">FROM</span>
	employees
<span class="token keyword">WHERE</span>
	last_name <span class="token operator">LIKE</span> <span class="token string">&#39;%a%&#39;</span><span class="token punctuation">;</span>
<span class="token comment">--------------------------------------</span>
查询员工名中第二个字符为_的员工名——用\转义
<span class="token keyword">SELECT</span>
	<span class="token operator">*</span>
<span class="token keyword">FROM</span>
	employees
<span class="token keyword">WHERE</span>
	last_name <span class="token operator">LIKE</span> <span class="token string">&#39;_\_%&#39;</span><span class="token punctuation">;</span>
	<span class="token operator">LIKE</span> <span class="token string">&#39;_$_%&#39;</span> <span class="token keyword">ESCAPE</span> <span class="token string">&#39;$&#39;</span><span class="token punctuation">;</span><span class="token comment">#把$设为转义字符</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>like特点:</p><ul><li><p>一般和通配符配合使用</p><p>通配符:% 任意多个字符，包含0个字符</p><p>_ 任意单个字符</p></li></ul><p>between and</p><ul><li>可以提高语句简洁度</li><li>包含临界值</li><li>两个临界值不能不要调换顺序</li></ul><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>#查询员工编号在100到120之间的元素信息
SELECT
	*
FROM
	employees
WHERE
	employee_id BETWEEN 100 AND 120;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="in" tabindex="-1"><a class="header-anchor" href="#in" aria-hidden="true">#</a> in</h4><p>判断某字段的值是否属于in列表中的某一项</p><p>特点：</p><ul><li>提高语句简洁度</li><li>in列表的值类型必须一致或兼容</li><li>不能使用通配符</li></ul><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>#查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
	last_name,
	job_id,
FROM
	employees
WHERE
	job_id IN(&#39;IT_PROG&#39;,&#39;AD_VP&#39;,&#39;AD_PRES&#39;);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="is-null" tabindex="-1"><a class="header-anchor" href="#is-null" aria-hidden="true">#</a> is null</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT
	last_name,
	job_id,
FROM
	employees
WHERE
	commission_pct IS NULL;
	IS NOT NULL;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="安全等于" tabindex="-1"><a class="header-anchor" href="#安全等于" aria-hidden="true">#</a> 安全等于&lt;=&gt;</h4><p>xxx&lt;=&gt;null;</p><p>IS NULL:仅仅可以判断NULL值，可读性较高，建议使用 &lt;=&gt;:既可以判断NULL值，又可以判断普通的数值，可读性较低</p><h3 id="排序查询" tabindex="-1"><a class="header-anchor" href="#排序查询" aria-hidden="true">#</a> 排序查询</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> 查询列表
<span class="token keyword">from</span> 表
【<span class="token keyword">where</span> 筛选条件】
<span class="token keyword">order</span> <span class="token keyword">by</span> 排序列表【<span class="token keyword">asc</span><span class="token operator">|</span><span class="token keyword">desc</span>】
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>特点:</p><ul><li><p>asc代表升序、desc代表降序</p><p>如果不写，默认是升序</p><p>按别名、表达式、函数、多个字段排序</p></li><li><p>order by子句中可以支持单个字段、多个字段、表达式、函数、别名</p></li><li><p>order by子句一般是放在查询语句的最后面，limit子句除外</p></li></ul><h2 id="常见函数" tabindex="-1"><a class="header-anchor" href="#常见函数" aria-hidden="true">#</a> 常见函数</h2><p>功能：类似Java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名</p><p>好处：</p><ol><li>隐藏了实现细节</li><li>提高代码的重用性</li></ol><p>调用：<code>select 函数名() 【from 表】;</code></p><p>特点：</p><ol><li>函数名</li><li>函数功能</li></ol><p>分类：</p><ul><li><p>单行函数</p><p>如concat、length、ifnull等</p></li><li><p>分组函数</p><p>功能:做统计使用、又称为统计函数、聚合函数、组函数</p></li></ul><h3 id="单行函数" tabindex="-1"><a class="header-anchor" href="#单行函数" aria-hidden="true">#</a> 单行函数</h3><ul><li>字符函数</li><li>数学函数</li><li>日期函数</li><li>其他函数【补充】</li><li>流程控制函数【补充】</li></ul><h4 id="字符函数" tabindex="-1"><a class="header-anchor" href="#字符函数" aria-hidden="true">#</a> 字符函数</h4><h4 id="length" tabindex="-1"><a class="header-anchor" href="#length" aria-hidden="true">#</a> length</h4><p>获取参数值的字节个数</p><h4 id="concat拼接字符串" tabindex="-1"><a class="header-anchor" href="#concat拼接字符串" aria-hidden="true">#</a> concat拼接字符串</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT CONCAT(last_name,&#39;_&#39;,first_name) 姓名 FROM employees;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="upper、lower" tabindex="-1"><a class="header-anchor" href="#upper、lower" aria-hidden="true">#</a> upper、lower</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT UPPER(&#39;john&#39;);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="substr、substring" tabindex="-1"><a class="header-anchor" href="#substr、substring" aria-hidden="true">#</a> substr、substring</h4><p>注意：索引从1开始</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT SUBSTR(&#39;1234567&#39;，6) out_put;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p><code>SELECT SUBSTR(&#39;1234567&#39;，1，3) out_put;</code>123</p><h4 id="instr" tabindex="-1"><a class="header-anchor" href="#instr" aria-hidden="true">#</a> instr</h4><p>返回字串 第一次出现的索引，如果找不到返回0</p><p><code>INSTR(&#39;1234&#39;,&#39;34&#39;);</code>3</p><h4 id="trim去掉前后的字符" tabindex="-1"><a class="header-anchor" href="#trim去掉前后的字符" aria-hidden="true">#</a> trim去掉前后的字符</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>trim(&#39; solfj &#39;)
trim(&#39;a&#39; from &#39;aaaaaaaaa出aaaaaaaa绝健康的aaaaaaaaaaaaaaa&#39;)
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="lpad用指定的字符实现左填充指定的长度" tabindex="-1"><a class="header-anchor" href="#lpad用指定的字符实现左填充指定的长度" aria-hidden="true">#</a> lpad用指定的字符实现左填充指定的长度</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT LPAD （’asdf&#39;,2,&#39;*&#39;) as out_put;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="rpad" tabindex="-1"><a class="header-anchor" href="#rpad" aria-hidden="true">#</a> rpad</h4><p>replace 替换</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT REPLCE(&#39;张无忌爱上了周芷若&#39;,&#39;周芷若&#39;,&#39;赵敏&#39;) as out_put;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>---------&gt;张无忌爱上了赵敏</p><h3 id="数学函数" tabindex="-1"><a class="header-anchor" href="#数学函数" aria-hidden="true">#</a> 数学函数</h3><h4 id="round-四舍五入" tabindex="-1"><a class="header-anchor" href="#round-四舍五入" aria-hidden="true">#</a> round 四舍五入</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT ROUND(-1.45);
SELECT ROUND(-1.567,2);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="celi-向上取整" tabindex="-1"><a class="header-anchor" href="#celi-向上取整" aria-hidden="true">#</a> celi 向上取整</h4><p>返回大于等于该参数的最小整数</p><p>1.52---&gt;2</p><h4 id="floor向下取整" tabindex="-1"><a class="header-anchor" href="#floor向下取整" aria-hidden="true">#</a> floor向下取整</h4><p>&lt;=</p><h4 id="truncate-截断" tabindex="-1"><a class="header-anchor" href="#truncate-截断" aria-hidden="true">#</a> truncate 截断</h4><p><code>SELECT TRUNCATE(1.699999,1);</code>1.6</p><h4 id="取余" tabindex="-1"><a class="header-anchor" href="#取余" aria-hidden="true">#</a> 取余</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>MOD(a,b)`---&gt;`a-a/b*b
SELECT MOD(-10,3)
SELECT 10%3;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="日期函数" tabindex="-1"><a class="header-anchor" href="#日期函数" aria-hidden="true">#</a> 日期函数</h3><h4 id="now" tabindex="-1"><a class="header-anchor" href="#now" aria-hidden="true">#</a> now</h4><p>返回当前系统日期+时间</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT NOW()
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="curdate" tabindex="-1"><a class="header-anchor" href="#curdate" aria-hidden="true">#</a> curdate</h4><p>返回当前系统日期，不包含时间</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT CURDATE();
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="curtime" tabindex="-1"><a class="header-anchor" href="#curtime" aria-hidden="true">#</a> curtime</h4><p>返回当前时间，不包含日期</p><p>SELECT CURTIME();</p><p>可以获取指定的部分，年、月、日、小时、分钟、秒</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT YEAR(NOW()) 年;
SELECT YEAR(&#39;1998-1-1&#39;) 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="str-to-date" tabindex="-1"><a class="header-anchor" href="#str-to-date" aria-hidden="true">#</a> str_to_date</h4><p>将日期格式的字符转换成指定格式的日期</p><p>`STR_TO_DATE(&#39;9-13-1999&#39;,&#39;%m-%d-%Y&#39;)</p><h4 id="date-format" tabindex="-1"><a class="header-anchor" href="#date-format" aria-hidden="true">#</a> date_format</h4><p>将日期转换成字符</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>DATE_FORMAT(&#39;2018/6/6&#39;,&#39;%Y年%m月%d日&#39;)
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><table><thead><tr><th style="text-align:center;">格式符</th><th style="text-align:center;">功能</th></tr></thead><tbody><tr><td style="text-align:center;">%Y</td><td style="text-align:center;">四位的年份</td></tr><tr><td style="text-align:center;">%y</td><td style="text-align:center;">二位的年份</td></tr><tr><td style="text-align:center;">%m</td><td style="text-align:center;">月份(01,02...11,12)</td></tr><tr><td style="text-align:center;">%c</td><td style="text-align:center;">月份(1,2,...11,12)</td></tr><tr><td style="text-align:center;">%d</td><td style="text-align:center;">日(01,02...)</td></tr><tr><td style="text-align:center;">%H</td><td style="text-align:center;">小时（24小时制）</td></tr><tr><td style="text-align:center;">%h</td><td style="text-align:center;">小时（12小时制）</td></tr><tr><td style="text-align:center;">%i</td><td style="text-align:center;">分钟(00,01...59)</td></tr><tr><td style="text-align:center;">%s</td><td style="text-align:center;">秒(00,01...59)</td></tr></tbody></table><p>相差天数DATEDIFF</p><h3 id="其他函数" tabindex="-1"><a class="header-anchor" href="#其他函数" aria-hidden="true">#</a> 其他函数</h3><p>SELECT VERSION();</p><p>SELECT DATABASE();</p><p>SELECT USER();</p><h3 id="流程控制函数" tabindex="-1"><a class="header-anchor" href="#流程控制函数" aria-hidden="true">#</a> 流程控制函数</h3><ol><li>if函数： if else 的效果</li></ol><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT IF(10&lt;5,&#39;大&#39;,&#39;小&#39;);
SELECT IF(a,b,c);
------------
if(a)
	b;
else
	c;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><ol><li><p>case函数</p><p>使用一</p></li></ol><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end


部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>使用二：类似于 多重if</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end

#案例：查询员工的工资的情况
如果工资&gt;20000,显示A级别
如果工资&gt;15000,显示B级别
如果工资&gt;10000，显示C级别
否则，显示D级别


SELECT salary,
CASE 
WHEN salary&gt;20000 THEN &#39;A&#39;
WHEN salary&gt;15000 THEN &#39;B&#39;
WHEN salary&gt;10000 THEN &#39;C&#39;
ELSE &#39;D&#39;
END AS 工资级别
FROM employees;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="分组函数" tabindex="-1"><a class="header-anchor" href="#分组函数" aria-hidden="true">#</a> 分组函数</h3><p>功能：用作统计使用，又称为聚合函数或统计函数或组函数</p><p>分类： sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数</p><p>特点：</p><ol><li><p>sum、avg一般用于处理数值型 max、min、count可以处理任何类型</p></li><li><p>以上分组函数都忽略null值</p></li><li><p>可以和distinct搭配实现去重的运算</p></li><li><p>count函数的单独介绍</p><p>一般使用count(*)用作统计行数</p></li><li><p>和分组函数一同查询的字段要求是group by后的字段count</p></li></ol><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT COUNT(salary) FROM employees;


SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;

效率：
MYISAM存储引擎下  ，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="分组查询" tabindex="-1"><a class="header-anchor" href="#分组查询" aria-hidden="true">#</a> 分组查询</h3><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>特点：</p><ol><li><p>和分组函数一同查询的字段必须是group by后出现的字段</p></li><li><p>筛选分为两类：分组前筛选和分组后筛选</p><table><thead><tr><th style="text-align:center;"></th><th style="text-align:center;">数据源</th><th style="text-align:center;">位置</th><th style="text-align:center;">连接的关键字</th></tr></thead><tbody><tr><td style="text-align:center;">分组前筛选</td><td style="text-align:center;">原始表</td><td style="text-align:center;">group by前</td><td style="text-align:center;">where</td></tr><tr><td style="text-align:center;">分组后筛选</td><td style="text-align:center;">分组后的结果集</td><td style="text-align:center;">group by后</td><td style="text-align:center;">having</td></tr></tbody></table><p>问题1：分组函数做筛选能不能放在where后面 答：不能，分组函数做条件肯定是放在having子句中</p><p>问题2：where——group by——having</p><p>一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率</p></li><li><p>分组可以按单个字段也可以按多个字段</p></li><li><p>可以搭配着排序使用</p></li></ol><h3 id="连接查询" tabindex="-1"><a class="header-anchor" href="#连接查询" aria-hidden="true">#</a> 连接查询</h3><p>含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询</p><p>笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行</p><p>发生原因：没有有效的连接条件 如何避免：添加有效的连接条件</p><p>分类：</p><ul><li>按年代分类： <ul><li>sql92标准:仅仅支持内连接</li><li>sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接</li></ul></li><li>按功能分类： <ul><li>内连接： <ul><li>等值连接</li><li>非等值连接</li><li>自连接</li></ul></li><li>外连接： <ul><li>左外连接</li><li>右外连接</li><li>全外连接</li></ul></li><li>交叉连接</li></ul></li></ul><h3 id="自连接" tabindex="-1"><a class="header-anchor" href="#自连接" aria-hidden="true">#</a> 自连接</h3><h3 id="sql99语法" tabindex="-1"><a class="header-anchor" href="#sql99语法" aria-hidden="true">#</a> sql99语法</h3><p>语法：</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>select 查询列表
from 表1 别名 【连接类型】
join 表2 别名 
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>分类：</p><ul><li>内连接（★）：inner</li><li>外连接 <ul><li>左外(★):left 【outer】</li><li>右外(★)：right 【outer】</li><li>全外：full【outer】</li></ul></li><li>交叉连接：cross</li></ul><h4 id="内连接" tabindex="-1"><a class="header-anchor" href="#内连接" aria-hidden="true">#</a> 内连接</h4><p>语法：</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>分类：</p><ul><li>等值</li><li>非等值</li><li>自连接</li></ul><p>特点：</p><ol><li>添加排序、分组、筛选</li><li>inner可以省略</li><li>筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读</li><li>inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集</li></ol><p>应用场景：用于查询一个表中有，另一个表没有的记录</p><p>特点：</p><ol><li><p>外连接的查询结果为主表中的所有记录</p><p>如果从表中有和它匹配的，则显示匹配的值</p><p>如果从表中没有和它匹配的，则显示null</p><p>外连接查询结果=内连接结果+主表中有而从表没有的记录</p></li><li><p>左外连接，left join左边的是主表 右外连接，right join右边的是主表</p></li><li><p>左外和右外交换两个表的顺序，可以实现同样的效果</p></li><li><p>全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的</p></li></ol><h4 id="交叉连接" tabindex="-1"><a class="header-anchor" href="#交叉连接" aria-hidden="true">#</a> 交叉连接</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code> SELECT b.*,bo.
 FROM beauty b
 CROSS JOIN boys bo;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="sql92和-sql99pk" tabindex="-1"><a class="header-anchor" href="#sql92和-sql99pk" aria-hidden="true">#</a> sql92和 sql99pk</h4><ul><li>功能：sql99支持的较多</li><li>可读性：sql99实现连接条件和筛选条件的分离，可读性较高</li></ul><h3 id="子查询" tabindex="-1"><a class="header-anchor" href="#子查询" aria-hidden="true">#</a> 子查询</h3><h4 id="含义" tabindex="-1"><a class="header-anchor" href="#含义" aria-hidden="true">#</a> 含义：</h4><p>出现在其他语句中的select语句，称为子查询或内查询</p><p>外部的查询语句，称为主查询或外查询</p><p>分类：</p><ul><li>按子查询出现的位置： <ul><li>select后面： <ul><li>仅仅支持标量子查询</li></ul></li><li>from后面： <ul><li>支持表子查询</li></ul></li><li>where或having后面：★ <ul><li>标量子查询（单行） √</li><li>列子查询 （多行） √</li><li>行子查询</li></ul></li><li>exists后面（相关子查询） <ul><li>表子查询</li></ul></li></ul></li><li>按结果集的行列数不同： <ul><li>标量子查询（结果集只有一行一列）</li><li>列子查询（结果集只有一列多行）</li><li>行子查询（结果集有一行多列）</li><li>表子查询（结果集一般为多行多列）</li></ul></li></ul><h5 id="where或having后面" tabindex="-1"><a class="header-anchor" href="#where或having后面" aria-hidden="true">#</a> where或having后面</h5><ol><li>标量子查询（单行子查询）</li><li>列子查询（多行子查询）</li><li>行子查询（多列多行）</li></ol><p>特点：</p><ul><li><p>子查询放在小括号内</p></li><li><p>子查询一般放在条件的右侧</p></li><li><p>标量子查询，一般搭配着单行操作符使用</p><p><code>&gt; &lt; &gt;= &lt;= = &lt;&gt;</code></p><p>列子查询，一般搭配着多行操作符使用</p><p>in、any/some、all</p></li><li><p>子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果</p></li></ul><p>行子查询（结果集一行多列或多行多列）</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>#案例：查询员工编号最小并且工资最高的员工信息
SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h5 id="select后面" tabindex="-1"><a class="header-anchor" href="#select后面" aria-hidden="true">#</a> select后面</h5><p>不会</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>#案例：查询每个部门的员工个数
SELECT d.*,(

	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h5 id="from后面" tabindex="-1"><a class="header-anchor" href="#from后面" aria-hidden="true">#</a> from后面</h5><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id


SELECT * FROM job_grades;


#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="exist" tabindex="-1"><a class="header-anchor" href="#exist" aria-hidden="true">#</a> exist</h4><h4 id="分页查询" tabindex="-1"><a class="header-anchor" href="#分页查询" aria-hidden="true">#</a> 分页查询</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>limit offset,size
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>offset要显示条目的其实索引（从0开始）</p><p>size要显示的条目个数</p><p>放在查询语句的最后</p><p>要显示的页数page，每页的条目数size</p><p>limit (page-1)*size,size)</p><h4 id="联合查询" tabindex="-1"><a class="header-anchor" href="#联合查询" aria-hidden="true">#</a> 联合查询</h4><p>union 联合 合并：将多条查询语句的结果合并成一个结果</p><p>语法：</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>查询语句1
union
查询语句2
union
...
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>应用场景： 要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时</p><p>特点：★</p><ol><li>要求多条查询语句的查询列数是一致的！</li><li>要求多条查询语句的查询的每一列的类型和顺序最好一致</li><li>union关键字默认去重，如果使用union all 可以包含重复项</li></ol><h3 id="插入语句" tabindex="-1"><a class="header-anchor" href="#插入语句" aria-hidden="true">#</a> 插入语句</h3><h4 id="方式一-经典的插入" tabindex="-1"><a class="header-anchor" href="#方式一-经典的插入" aria-hidden="true">#</a> 方式一：经典的插入</h4><ul><li><p>语法：</p><p>insert into 表名(列名,...) values(值1,...);</p></li></ul><ol><li>插入的值的类型要与列的类型一致或兼容</li><li>不可以为null的列必须插入值。</li><li>列数和值的个数必须一致</li><li>可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致</li></ol><h4 id="方式二" tabindex="-1"><a class="header-anchor" href="#方式二" aria-hidden="true">#</a> 方式二</h4><p>语法： insert into 表名 set 列名=值,列名=值,..</p><p>INSERT INTO beauty SET id=19,NAME=&#39;刘涛&#39;,phone=&#39;999&#39;;</p><ol><li><p>方式一支持插入多行,方式二不支持</p></li><li><p>方式一支持子查询，方式二不支持</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>INSERT INTO beauty(id,NAME,phone)
SELECT 26,&#39;宋茜&#39;,&#39;11809866&#39;;

INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,&#39;1234567&#39;
FROM boys WHERE id&lt;3;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div></li></ol><h3 id="修改语句" tabindex="-1"><a class="header-anchor" href="#修改语句" aria-hidden="true">#</a> 修改语句</h3><ol><li><p>修改单表的记录★</p><p>语法：</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>update 表名
set 列=新值,列=新值,...
where 筛选条件;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div></li><li><p>修改多表的记录【补充】</p><p>语法：</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>sql92语法：
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;

sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div></li></ol><h3 id="删除语句" tabindex="-1"><a class="header-anchor" href="#删除语句" aria-hidden="true">#</a> 删除语句</h3><ul><li><p>方式一：delete 语法：</p><ol><li>单表的删除【★】 delete from 表名 where 筛选条件</li><li>多表的删除【补充】</li></ol><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>sql92语法：
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

sql99语法：

delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div></li><li><p>方式二：truncate</p><p>语法：truncate table 表名;</p></li></ul><ol><li>delete 可以加where 条件，truncate不能加</li><li>truncate删除，效率高一丢丢</li><li>假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始。</li><li>truncate删除没有返回值，delete删除有返回值</li><li>truncate删除不能回滚，delete删除可以回滚.</li></ol><h3 id="数据定义语言" tabindex="-1"><a class="header-anchor" href="#数据定义语言" aria-hidden="true">#</a> 数据定义语言</h3><h4 id="库和表的管理" tabindex="-1"><a class="header-anchor" href="#库和表的管理" aria-hidden="true">#</a> 库和表的管理</h4><p>库的管理</p><p>创建、修改、删除</p><p>表的管理</p><p>创建、修改、删除</p><p>创建： create 修改： alter 删除： drop</p><h4 id="库的管理" tabindex="-1"><a class="header-anchor" href="#库的管理" aria-hidden="true">#</a> 库的管理</h4><ol><li><p>库的创建</p><p>语法： <code>create database [if not exists]库名;</code></p></li></ol><h5 id="库的修改" tabindex="-1"><a class="header-anchor" href="#库的修改" aria-hidden="true">#</a> 库的修改</h5><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>RENAME DATABASE books TO 新库名;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>更改库的字符集</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>ALTER DATABASE books CHARACTER SET gbk;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>库的删除</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>DROP DATABASE IF EXISTS books;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="表的管理" tabindex="-1"><a class="header-anchor" href="#表的管理" aria-hidden="true">#</a> 表的管理</h4><h5 id="表的创建-★" tabindex="-1"><a class="header-anchor" href="#表的创建-★" aria-hidden="true">#</a> 表的创建 ★</h5><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>语法：
create table 表名(
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	...
	列名 列的类型【(长度) 约束】
)
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h5 id="表的修改" tabindex="-1"><a class="header-anchor" href="#表的修改" aria-hidden="true">#</a> 表的修改</h5><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><ol><li><p>修改列名 <code>ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;</code></p></li><li><p>修改列的类型或约束</p><p><code>ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;</code></p></li><li><p>添加新列 <code>ALTER TABLE author ADD COLUMN annual DOUBLE;</code></p></li><li><p>删除列</p><p><code>ALTER TABLE book_author DROP COLUMN annual;</code></p></li><li><p>修改表名</p><p><code>ALTER TABLE author RENAME TO book_author;</code></p></li></ol><h5 id="表的删除" tabindex="-1"><a class="header-anchor" href="#表的删除" aria-hidden="true">#</a> 表的删除</h5><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>DROP TABLE 【IF EXISTS】 表名;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h5 id="表的复制" tabindex="-1"><a class="header-anchor" href="#表的复制" aria-hidden="true">#</a> 表的复制</h5><p>仅仅复制表的结构</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>CREATE TABLE copy LIKE author;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>复制表的结构+数据</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>CREATE TABLE copy2 SELECT * FROM author;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>只复制部分数据</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>CREATE TABLE copy3 SELECT id,au_name FROM author WHERE nation=&#39;中国&#39;;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>仅仅复制某些字段</p><p><code>CREATE TABLE copy4 SELECT id,au_name FROM author WHERE 0;</code>( 1=2)(where null)</p><h3 id="数据类型" tabindex="-1"><a class="header-anchor" href="#数据类型" aria-hidden="true">#</a> 数据类型</h3><ul><li>数值型： <ul><li>整型</li><li>小数： <ul><li>定点数</li><li>浮点数</li></ul></li></ul></li><li>字符型： <ul><li>较短的文本：char、varchar</li><li>较长的文本：text、blob（较长的二进制数据）</li></ul></li><li>日期型：</li></ul><h4 id="整型" tabindex="-1"><a class="header-anchor" href="#整型" aria-hidden="true">#</a> 整型</h4><table><thead><tr><th style="text-align:center;">分类</th><th style="text-align:center;">字节</th></tr></thead><tbody><tr><td style="text-align:center;">tinyint</td><td style="text-align:center;">1</td></tr><tr><td style="text-align:center;">smallint</td><td style="text-align:center;">2</td></tr><tr><td style="text-align:center;">mediumint</td><td style="text-align:center;">3</td></tr><tr><td style="text-align:center;">int/integer</td><td style="text-align:center;">4</td></tr><tr><td style="text-align:center;">bigint</td><td style="text-align:center;">8</td></tr></tbody></table><p>特点：</p><ol><li><p>如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字</p></li><li><p>如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值</p></li><li><p>如果不设置长度，会有默认的长度 长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！</p></li><li><p>如何设置无符号和有符号</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT(7) ZEROFILL,//默认无符号
    t2 INT(7) UNSIGNED,
);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div></li></ol><h5 id="小数" tabindex="-1"><a class="header-anchor" href="#小数" aria-hidden="true">#</a> 小数</h5><p>分类：</p><ol><li><p>浮点型</p><ul><li>float(M,D)</li><li>double(M,D)</li></ul><p>2.定点型</p><ul><li>dec(M，D)</li><li>decimal(M,D)</li></ul></li></ol><p>特点：</p><ol><li>M：整数部位+小数部位 D：小数部位 如果超过范围，则插入临界值</li><li>M和D都可以省略 如果是decimal，则M默认为10，D默认为0 如果是float和double，则会根据插入的数值的精度来决定精度</li><li>定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用</li></ol><p>所选择的类型越简单越好，能保存数值的类型越小越好</p><h4 id="字符型" tabindex="-1"><a class="header-anchor" href="#字符型" aria-hidden="true">#</a> 字符型</h4><ul><li>较短的文本： <ul><li>char</li><li>varchar</li></ul></li><li>其他： <ul><li>binary和varbinary用于保存较短的二进制</li><li>enum用于保存枚举</li><li>set用于保存集合</li></ul></li><li>较长的文本： <ul><li>text</li><li>blob(较大的二进制)</li></ul></li></ul><p>特点:</p><table><thead><tr><th></th><th>写法</th><th>M的意思</th><th>特点</th><th>空间的耗费</th><th>效率</th></tr></thead><tbody><tr><td>char</td><td>char(M)</td><td>最大的字符数，可以省略，默认为1</td><td>固定长度的字符</td><td>比较耗费</td><td>高</td></tr><tr><td>varchar</td><td>varchar(M)</td><td>最大的字符数不可省略</td><td>可变长度的字符</td><td>比较节省</td><td>低</td></tr></tbody></table><h5 id="enum类型" tabindex="-1"><a class="header-anchor" href="#enum类型" aria-hidden="true">#</a> Enum类型</h5><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>CREATE TABLE tab_char(
	c1 ENUM(&#39;a&#39;,&#39;b&#39;,&#39;c&#39;)
);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h5 id="set类型" tabindex="-1"><a class="header-anchor" href="#set类型" aria-hidden="true">#</a> set类型</h5><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>CREATE TABLE tab_set(
	s1 SET(&#39;a&#39;,&#39;b&#39;,&#39;c&#39;,&#39;d&#39;)
);
INSERT INTO tab_set VALUES(&#39;a&#39;);
INSERT INTO tab_set VALUES(&#39;A,B&#39;);
INSERT INTO tab_set VALUES(&#39;a,c,d&#39;);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="日期类型" tabindex="-1"><a class="header-anchor" href="#日期类型" aria-hidden="true">#</a> 日期类型</h4><table><thead><tr><th>日期和时间类型</th><th>字节</th><th>最小值</th><th>最大值</th></tr></thead><tbody><tr><td>date</td><td>4</td><td>1000-01-01</td><td>9999-12-31</td></tr><tr><td>datetime不受时区影响</td><td>8</td><td>1000-01-01 00:00:00</td><td>9999-12-32 23:59:59</td></tr><tr><td>timestamp受时区影响</td><td>4</td><td>19700101080001</td><td>2038年的某个时刻</td></tr><tr><td>time</td><td>3</td><td>-838:59:59</td><td>838:59:59</td></tr><tr><td>year</td><td>1</td><td>1901</td><td>2155</td></tr></tbody></table><h4 id="常见约束" tabindex="-1"><a class="header-anchor" href="#常见约束" aria-hidden="true">#</a> 常见约束</h4><h5 id="含义-1" tabindex="-1"><a class="header-anchor" href="#含义-1" aria-hidden="true">#</a> 含义</h5><p>一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性</p><h5 id="分类-六大约束" tabindex="-1"><a class="header-anchor" href="#分类-六大约束" aria-hidden="true">#</a> 分类：六大约束</h5><ul><li>NOT NULL：非空，用于保证该字段的值不能为空 比如姓名、学号等</li><li>DEFAULT:默认，用于保证该字段有默认值 比如性别</li><li>PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空 比如学号、员工编号等</li><li>UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空 比如座位号</li><li>CHECK:检查约束【mysql中不支持】 比如年龄、性别</li><li>FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值 在从表添加外键约束，用于引用主表中某列的值 比如学生表的专业编号，员工表的部门编号，员工表的工种编号</li></ul><p>添加约束的时机：</p><ol><li>创建表时</li><li>修改表时</li></ol><p>约束的添加分类：</p><ul><li><p>列级约束：</p><p>六大约束语法上都支持，但外键约束没有效果</p></li><li><p>表级约束：</p><p>除了非空、默认，其他的都支持</p></li></ul><h5 id="列级约束" tabindex="-1"><a class="header-anchor" href="#列级约束" aria-hidden="true">#</a> 列级约束</h5><p>语法：</p><p>直接在字段名和类型后面追加 约束类型即可。</p><p>只支持：默认、非空、主键、唯一</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL UNIQUE,#非空
	gender CHAR(1) CHECK(gender=&#39;男&#39; OR gender =&#39;女&#39;),#检查,不支持
	seat INT UNIQUE,#唯一
	age INT DEFAULT  18,#默认约束
	majorId INT REFERENCES major(id)#外键，不支持

);


CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>查看stuinfo中的所有索引，包括主键、外键、唯一 SHOW INDEX FROM stuinfo;</p><h4 id="表级约束" tabindex="-1"><a class="header-anchor" href="#表级约束" aria-hidden="true">#</a> 表级约束</h4><p>语法：在各个字段的最下面</p><p>【constraint 约束名】 约束类型(字段名)</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender =&#39;男&#39; OR gender  = &#39;女&#39;),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
	
);
SHOW INDEX FROM stuinfo;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h5 id="通用的写法-★" tabindex="-1"><a class="header-anchor" href="#通用的写法-★" aria-hidden="true">#</a> 通用的写法：★</h5><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="修改表时添加约束" tabindex="-1"><a class="header-anchor" href="#修改表时添加约束" aria-hidden="true">#</a> 修改表时添加约束</h4><p>1、添加列级约束 alter table 表名 modify column 字段名 字段类型 新约束;</p><p>2、添加表级约束 alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="修改表时删除约束" tabindex="-1"><a class="header-anchor" href="#修改表时删除约束" aria-hidden="true">#</a> 修改表时删除约束</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="标识列" tabindex="-1"><a class="header-anchor" href="#标识列" aria-hidden="true">#</a> 标识列</h3><p>又称为自增长列</p><p>含义：可以不用手动的插入值，系统提供默认的序列值</p><p>特点：</p><ol><li>标识列必须和主键搭配吗？不一定，但要求是一个key</li><li>一个表可以有几个标识列？至多一个！</li><li>标识列的类型只能是数值型</li><li>标识列可以通过 <code>SET auto_increment_increment=3;</code>设置步长可以通过 手动插入值，设置起始值</li></ol><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>#一、创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT  ,
	NAME FLOAT UNIQUE AUTO_INCREMENT,
	seat INT 
);
TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity(id,NAME) VALUES(NULL,&#39;john&#39;);
INSERT INTO tab_identity(NAME) VALUES(&#39;lucy&#39;);
SELECT * FROM tab_identity;
SHOW VARIABLES LIKE &#39;%auto_increment%&#39;;
SET auto_increment_increment=3;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>查看支持的存储引擎<code>SHOW ENGINES;</code></p><h3 id="事务" tabindex="-1"><a class="header-anchor" href="#事务" aria-hidden="true">#</a> 事务</h3><p>隐式事务：事务没有明显的开启和结束的标记</p><p>显式事务：事务具有明显的开启和结束的标记</p><p>前提：必须先设置自动提交功能为禁用</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...

步骤3：结束事务
commit;提交事务
rollback;回滚事务

savepoint 节点名;设置保存点
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>truncate不能回滚</p><h4 id="事务的隔离级别" tabindex="-1"><a class="header-anchor" href="#事务的隔离级别" aria-hidden="true">#</a> 事务的隔离级别</h4><table><thead><tr><th style="text-align:center;"></th><th style="text-align:center;">脏读</th><th style="text-align:center;">不可重复读</th><th style="text-align:center;">幻读</th></tr></thead><tbody><tr><td style="text-align:center;">read uncommitted</td><td style="text-align:center;">√</td><td style="text-align:center;">√</td><td style="text-align:center;">√</td></tr><tr><td style="text-align:center;">read committed</td><td style="text-align:center;">×</td><td style="text-align:center;">√</td><td style="text-align:center;">√</td></tr><tr><td style="text-align:center;">repeatable read</td><td style="text-align:center;">×</td><td style="text-align:center;">×</td><td style="text-align:center;">√</td></tr><tr><td style="text-align:center;">serializable</td><td style="text-align:center;">×</td><td style="text-align:center;">×</td><td style="text-align:center;">×</td></tr></tbody></table><p>mysql中默认 第三个隔离级别 repeatable read oracle中默认第二个隔离级别 read committed 查看隔离级别 <code>select @@tx_isolation;</code> 设置隔离级别 <code>set session|global transaction isolation level 隔离级别;</code></p><h4 id="演示savepoint-的使用" tabindex="-1"><a class="header-anchor" href="#演示savepoint-的使用" aria-hidden="true">#</a> 演示savepoint 的使用</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

SELECT * FROM account;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="视图" tabindex="-1"><a class="header-anchor" href="#视图" aria-hidden="true">#</a> 视图</h3><p>含义：虚拟表，和普通表一样使用 mysql5.1版本出现的新特性，是通过表动态生成的数据</p><p>比如：舞蹈班和普通班级的对比</p><table><thead><tr><th style="text-align:center;"></th><th style="text-align:center;">创建语法的关键字</th><th style="text-align:center;">是否实际占用物理空间</th><th style="text-align:center;">使用</th></tr></thead><tbody><tr><td style="text-align:center;">视图</td><td style="text-align:center;">create view</td><td style="text-align:center;">只是保存了sql逻辑</td><td style="text-align:center;">增删改查，只是一般不能增删改</td></tr><tr><td style="text-align:center;">表</td><td style="text-align:center;">create table</td><td style="text-align:center;">保存了数据</td><td style="text-align:center;">增删改查</td></tr></tbody></table><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>#案例：查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`
WHERE s.`stuname` LIKE &#39;张%&#39;;

CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`;

SELECT * FROM v1 WHERE stuname LIKE &#39;张%&#39;;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="创建" tabindex="-1"><a class="header-anchor" href="#创建" aria-hidden="true">#</a> 创建</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>语法： create view 视图名 as 查询语句;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="修改" tabindex="-1"><a class="header-anchor" href="#修改" aria-hidden="true">#</a> 修改</h4><p>方式一：</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>create or replace view 视图名 as 查询语句;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>方式二： <code>语法： alter view 视图名 as 查询语句;</code></p><h4 id="删除" tabindex="-1"><a class="header-anchor" href="#删除" aria-hidden="true">#</a> 删除</h4><p>语法：<code>drop view 视图名,视图名,...;</code></p><h4 id="查看" tabindex="-1"><a class="header-anchor" href="#查看" aria-hidden="true">#</a> 查看</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>DESC myv3;
SHOW CREATE VIEW myv3;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h5 id="视图的更新" tabindex="-1"><a class="header-anchor" href="#视图的更新" aria-hidden="true">#</a> 视图的更新</h5><p>具备以下特点的视图不允许更新</p><p>包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all</p></div><!--[--><!--]--></div><footer class="page-meta"><div class="meta-item edit-link"><a class="external-link meta-item-label" href="https://gitee.com/Francis-xsc/francis-xsc/edit/main/program/数据库.md" rel="noopener noreferrer" target="_blank" aria-label="Edit this page"><!--[--><!--]--> Edit this page <span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span><!--[--><!--]--></a></div><div class="meta-item last-updated"><span class="meta-item-label">最后更新时间: </span><!----></div><div class="meta-item contributors"><span class="meta-item-label">Contributors: </span><span class="meta-item-info"><!--[--><!--[--><span class="contributor" title="email: 920364365@qq.com">Francis-xsc</span><!----><!--]--><!--]--></span></div></footer><!----><!--[--><!--]--></main><!--]--></div><!----><!--]--></div>
    <script type="module" src="/assets/app-6c74e544.js" defer></script>
  </body>
</html>
